# import xlrd
import os
import pandas as pd
import re
from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.QtWidgets import QFileDialog, QMessageBox
import hashlib
import datetime
import openpyxl
import pandas as pd

class Company:
    def __init__(self):
        self.data = {}
        self.name_list = []
        self.path = {}
        self.month = {}
    def get_data(self, one_file_path):
        file_dir, file_name = os.path.split(one_file_path)
        company_name = hashlib.md5(file_dir.encode()).hexdigest()
        if company_name not in self.name_list:
            df_colums, mon = self.get_column(file_name)
            self.data[company_name] = pd.DataFrame(columns=df_colums)
            self.path[company_name] = file_dir
            self.month[company_name] = mon
            self.name_list.append(company_name)
        return self.data.get(company_name)

    def __contains__(self, item):
        if item in self.name_list:
            return True
        else:
            return False

    def getDatesByTimes(self,sDateStr, eDateStr):
        list = []
        datestart = datetime.datetime.strptime(sDateStr, '%Y-%m-%d')
        dateend = datetime.datetime.strptime(eDateStr, '%Y-%m-%d')
        list.append(datestart.strftime('%Y-%m-%d'))
        while datestart < dateend:
            datestart += datetime.timedelta(days=1)
            list.append(datestart.strftime('%Y-%m-%d'))
        return list

    def get_column(self, file_name):
        start_time, end_time = self.get_date(file_name)
        col_start = ["编号","姓名","工种"]
        col_mid = list(self.getDatesByTimes(start_time, end_time))
        col_end = ["合计"]
        mon = col_mid[-1].split('-')[1]
        return col_start + col_mid + col_end, mon



    def get_date(self, file_name):
        # print(file_name)
        date_all = re.search(r'\d.*\d\S', file_name)#<re.Match object; span=(18, 40), match='2020年9月26日至2020年10月25日'>
        if date_all:
            date_all = date_all.group()
        else:
            return
        # print(date_all)
        start_time, end_time = date_all.split("至")
        start_time = start_time.replace("年","-").replace("月", "-").replace("日","")#2020-9-26
        end_time = end_time.replace("年","-").replace("月", "-").replace("日","")# 
        return start_time, end_time





    def get_project_info(self, one_file_path):
        file_dir, file_name = os.path.split(one_file_path)
        com_name = re.match(r"\S+", file_name)#<re.Match object; span=(0, 18), match='西安宝能汽车智慧项目一期工程创惠管理'>
        date_all = re.search(r'\d.*\d\S', file_name)#<re.Match object; span=(18, 40), match='2020年9月26日至2020年10月25日'>
        if com_name and date_all:
            com_name = com_name.group()
            date_all = date_all.group()
        else:
            return
        start_time, end_time = date_all.split("至")
        start_time = start_time.replace("年","-").replace("月", "-").replace("日","")#2020-9-26
        end_time = end_time.replace("年","-").replace("月", "-").replace("日","")#2020-10-25
        title = end_time.split('-')[1] + '月份劳务人员考勤汇总表'




class Ui_MainWindow(object):
    def __init__(self):
        self.file_path = ""
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(500, 200)
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.label = QtWidgets.QLabel(self.centralwidget)
        self.label.setGeometry(QtCore.QRect(40, 60, 71, 31))
        self.label.setObjectName("label")

        self.filePath = QtWidgets.QLineEdit(self.centralwidget)
        self.filePath.setGeometry(QtCore.QRect(110, 60, 250, 31))
        self.filePath.setObjectName("filePath")

        self.selectFile = QtWidgets.QPushButton(self.centralwidget)
        self.selectFile.setGeometry(QtCore.QRect(400, 60, 75, 31))
        self.selectFile.setObjectName("selectFile")
        self.selectFile.clicked.connect(self.msg)

        self.pushButton = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton.setGeometry(QtCore.QRect(190, 130, 80, 40))
        self.pushButton.setObjectName("pushButton")
        self.pushButton.clicked.connect(self.start)
        MainWindow.setCentralWidget(self.centralwidget)
        self.statusbar = QtWidgets.QStatusBar(MainWindow)
        self.statusbar.setObjectName("statusbar")
        MainWindow.setStatusBar(self.statusbar)

        self.retranslateUi(MainWindow)
        QtCore.QMetaObject.connectSlotsByName(MainWindow)

    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "考勤"))
        self.label.setText(_translate("MainWindow", "文件夹路径："))
        self.selectFile.setText(_translate("MainWindow", "选择文件夹"))
        self.pushButton.setText(_translate("MainWindow", "获取结果"))

    def msg(self):
        download_path = QFileDialog.getExistingDirectory(self.centralwidget, "选取文件夹", "C:/")
        
        self.file_path = str(download_path)
        self.filePath.setText(self.file_path)

    def get_sheet_datas(self, excel_path):
        wb = openpyxl.load_workbook(excel_path)
        lst = []
        for sheet in wb.sheetnames:
            return wb[sheet]
    #判断是文件夹还是文件
    #文件返回True,文件夹返回False
    def is_file(self, my_path):
        if os.path.isdir(my_path):
            return False
        else:
            return True
    # 1.入口，从GUI读取总路径
    def start(self):
        try:
            self.all_company = Company()
            self.path_handler(self.file_path)
            self.data_to_excel()
            QMessageBox.question(self.centralwidget, '导出成功', '导出考勤表成功！\n导出文件在原路径', QMessageBox.Yes, QMessageBox.Yes)
        except Exception as e:
            QMessageBox.question(self.centralwidget, '导出失败', e, QMessageBox.Yes, QMessageBox.Yes)
    # 2.读取路径下的文件、文件夹，如果是文件夹，递归。若是文件，开始3分析
    def path_handler(self, path):
        if self.is_file(path):
            mydf = self.run(path)
        else:
            path_lst = os.listdir(path)
            for i in path_lst:
                heihei = os.path.join(path, i)
                self.path_handler(os.path.join(path, i))
    
    # 3.根据文件路径名md5转码，认为是一家公司，添加到Company或者从Company获取df
    # 4.新公司获取数据 放入DF，
    def run(self, one_file_path):
        df = self.all_company.get_data(one_file_path)
        table = self.get_sheet_datas(one_file_path)
        index_dict = {}
        for index, row_data in enumerate(table.rows, 0):
            
            lst = list(map(lambda x: str(x.value) ,row_data))

            if index == 1:
                # print(lst)
                index_dict['姓名'] = lst.index('姓名')
                index_dict['班组'] = lst.index('班组')
                index_dict['工种'] = lst.index('工种')
                index_dict['日期'] = lst.index('日期')
                index_dict['打卡记录'] = lst.index('打卡记录')
            elif index > 1:
                name = lst[index_dict['姓名']]
                banzu = lst[index_dict['班组']]
                gongzhong = lst[index_dict['工种']]
                riqi = lst[index_dict['日期']].split()[0]
                clock_in = lst[index_dict['打卡记录']].split(",")
                # hour_diff = 1
                hour_diff = self.handler(clock_in)
                if len(df[df['姓名'] == name]) > 0:
                    x = df[df['姓名'] == name]['编号']-1
                    df.loc[x,riqi] = hour_diff
                else:
                    df.loc[len(df),['编号','姓名','工种']] = [len(df)+1, name, gongzhong]
                    df.loc[len(df)-1,riqi] = hour_diff
        # df.fillna('0')
        return df
                
    def handler(self, clock_in_lst):
        lst = []
        for i in clock_in_lst:
            if "进" in i:
                lst.append(1)
            else:
                lst.append(-1)
        diff = 0
        if len(lst) <= 1:
            return 0
        i = 0
        while i < len(lst)-1:
            #这一次和下一次都是进
            if lst[i] == 1 and lst[i+1] == 1:
                i=i+1
            # 这一次是进，下一次是出
            elif lst[i] == 1 and lst[i+1] == -1:
                come = int(clock_in_lst[i][:2]) * 60 + int(clock_in_lst[i][3:5])
                out = int(clock_in_lst[i+1][:2]) * 60 + int(clock_in_lst[i+1][3:5])
                diff = out - come + diff
                i = i + 2
            else:
                i = i + 1
        if diff > 0:
            return 1
        # elif diff >= 240:
        #     return 0.5
        else:
            return 0

    def data_to_excel(self):
        for company_name in self.all_company.name_list:
            dir_path = self.all_company.path[company_name]
            df = self.all_company.data[company_name]
            month = self.all_company.month[company_name]
            self.into_exce(df, dir_path, month)
            

    def into_exce(self, df, dir_path, month):
        wb = openpyxl.Workbook()
        ws = wb.active
        columns = list(df.columns)
        # print(columns)
        # print(columns[3:-1])
        # print(list(map(lambda x: int(x.split('-')[-1]),columns[3:-1])))
        columns[3:-1] = list(map(lambda x: int(x.split('-')[-1]),columns[3:-1]))
        ws.append(columns)
        for data_i in range(len(df)):
            lst = list(map(lambda x:str(x),df.iloc[data_i]))
            lst2 = [0 if i in ['nan','0'] else i for i in lst]
            lst3 = [int(i) if i in list(map(str,range(1000))) else i for i in lst2]
            lst4 = [0.5 if i == '0.5' else i for i in lst3]
            lst4[-1] = sum(lst4[3:-1])
            ws.append(lst4)
        col_n = ['D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R'
        ,'S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG']
        for i in col_n:
            ws.column_dimensions[i].width = 4
        wb.save(os.path.join(dir_path, month+'月考勤.xlsx'))


if __name__ == "__main__":
    import sys
    app = QtWidgets.QApplication(sys.argv)
    MainWindow = QtWidgets.QMainWindow()
    ui = Ui_MainWindow()
    ui.setupUi(MainWindow)
    MainWindow.show()
    sys.exit(app.exec_())

